using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

using phorm.database.core;
using phorm.model.fields;

namespace phorm.database.mssql
{
    /// <summary>
    /// A Microsoft SQL specific DatabaseRecord.
    /// </summary>
    public class MSSQLRecord : DatabaseRecord
    {
        /// <summary>
        /// Build table query of this record type from CRUD
        /// </summary>
        /// <returns>Create table query</returns>
        public override string create()
        {
            return CRUD.buildTable(this);
        }

        /// <summary>
        /// Initialize object with a specific primary key ID and get data from a ForeignKey object
        /// </summary>
        /// <param name="id">Primary Key ID of row to build</param>
        public override void initWithId(object id)
        {
            MSSQLDatabase.connection();
            SqlDataReader reader = MSSQLDatabase.query(CRUD.buildPrimaryKeyQuery(this, id));

            if (reader != null)
            {
                string alias = this.GetType().Name.ToLower();
                while (reader.Read())
                {
                    foreach (KeyValuePair<string, DatabaseColumn> column in this.columns)
                    {
                        if (!(column.Value is IIgnorableColumn))
                        {
                            this.columns[column.Key].value = reader[alias + "_" + column.Key].ToString();
                            if (column.Value is ForeignKey<DatabaseRecord>)
                            {
                                string newAlias = (column.Value as ForeignKey<DatabaseColumn>).relative.model.GetType().Name.ToLower();
                                foreach (KeyValuePair<string, DatabaseColumn> altColumn in (column.Value as ForeignKey<DatabaseColumn>).relative.model.columns)
                                {
                                    if (!(altColumn.Value is IIgnorableColumn))
                                    {
                                        altColumn.Value.value = reader[newAlias + "_" + altColumn.Key].ToString();
                                    }
                                }
                            }
                        }
                    }
                }
            }
            this.IsNew = false;
            MSSQLDatabase.finish();
        }

        /// <summary>
        /// Run CRUD's many-to-many relationship creation.
        /// </summary>
        /// <param name="column">Many-to-many column</param>
        /// <returns>pivot table create query</returns>
        public override string createManyToMany(BaseManyToMany column)
        {
            return CRUD.createManyToMany(column);
        }

        /// <summary>
        /// Save this object to the database using CRUD.
        /// </summary>
        public override void save()
        {
            MSSQLDatabase.connection();
            bool hasChanged = false;
            foreach (KeyValuePair<string, DatabaseColumn> column in this.columns)
            {
                if (!(column.Value is PrimaryKey) && column.Value.hasChanged)
                    hasChanged = true;
                if (column.Value is IAutoUpdateColumn)
                    (column.Value as IAutoUpdateColumn).update();
            }

            if (this.IsNew)
                this.primary_key.value = MSSQLDatabase.idQuery(CRUD.buildSaveQuery(this));
            else if (hasChanged)
                MSSQLDatabase.nonQuery(CRUD.buildSaveQuery(this));
            this.IsNew = false;
            MSSQLDatabase.finish();
        }

        /// <summary>
        /// Delete this object from the database using CRUD if it has a primary key set.
        /// </summary>
        public override void delete()
        {
            if (this.primary_key.value != null)
            {
                MSSQLDatabase.connection();
                MSSQLDatabase.nonQuery(CRUD.buildDeleteQuery(this));
                MSSQLDatabase.finish();
            }
        }

        /// <summary>
        /// Build empty query from CRUD.
        /// </summary>
        /// <returns>Empty query</returns>
        public override string empty()
        {
            return CRUD.buildEmptyQuery(this);
        }

        /// <summary>
        /// Build drop query from CRUD.
        /// </summary>
        /// <returns>Drop query</returns>
        public override string drop()
        {
            return CRUD.buildDropQuery(this);
        }
    }
}
